package com.zk.fm.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Statement;
import com.zk.fm.entity.Player;
import com.zk.fm.entity.User;

public class JDBCUtil {

	private static String url = "jdbc:mysql://zhengkang0301.xicp.net:3306/fmtools";
	private static String user = "root";
	private static String password = "root";
	private static Connection conn;
	private static Statement stmt;
	private static ResultSet rs;

	public static void main(String[] args) {
		try {
			connectDB();
			// suggest("aaa", "aaaaaaaaaaaa");
			closeDB();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static void closeRS() throws Exception {
		if (rs != null) {
			rs.close();
		}
	}

	public static void closeDB() throws Exception {
		if (rs != null) {
			rs.close();
		}
		if (stmt != null) {
			stmt.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	public static void connectDB() throws Exception {
		if (conn == null || conn.isClosed()) {
			Class.forName("org.gjt.mm.mysql.Driver");
			conn = DriverManager.getConnection(url, user, password);
			stmt = (Statement) conn.createStatement();
		}
	}

	public static boolean isAccountExist(String account) throws Exception {
		String url = "select count(*) from users where account='" + account
				+ "';";
		System.out.println("url = " + url);
		rs = stmt.executeQuery(url);
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
			System.out.println("count:" + count);
		}
		closeRS();
		return (count > 0);
	}

	// 0 exist ; 1 exception ; 2 success
	public static int register(User user) {
		try {
			boolean exist = isAccountExist(user.getAccount());
			if (exist) {
				return 0;
			}
			String url = "insert into users values(null,'" + user.getAccount()
					+ "','" + user.getPassword() + "');";
			System.out.println("url = " + url);
			stmt.executeUpdate(url);
		} catch (Exception e) {
			return 1;
		}
		return 2;
	}

	public static boolean login(User user) {
		try {
			String url = "select count(*) from users where account='"
					+ user.getAccount() + "' and password = '"
					+ user.getPassword() + "';";
			System.out.println("url = " + url);
			rs = stmt.executeQuery(url);
			int count = 0;
			while (rs.next()) {
				count = rs.getInt(1);
				System.out.println("count:" + count);
			}
			closeRS();
			return (count > 0);
		} catch (Exception e) {
			return false;
		}
	}

	public static boolean suggest(String account, String suggestion) {
		try {
			String url = "insert into suggestion values(null,'" + account
					+ "','" + suggestion + "',now());";
			System.out.println("url = " + url);
			stmt.executeUpdate(url);
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	public static List<Player> getPlayerByName(String name) throws Exception {
		String url;
		if (name == null || name.equals("")) {
			url = "select * from players;";
		} else {
			url = "select * from players where name like '%" + name + "%';";
		}
		System.out.println("url = " + url);
		rs = stmt.executeQuery(url);
		List<Player> players = new ArrayList<Player>();
		while (rs.next()) {
			Player player = new Player();
			int i = 1;
			player.setId(rs.getLong(i++));
			player.setName(rs.getString(i++));

			player.setCorners(rs.getInt(i++));
			player.setCrossing(rs.getInt(i++));
			player.setDribbling(rs.getInt(i++));
			player.setFinishing(rs.getInt(i++));
			player.setFirst_touch(rs.getInt(i++));
			player.setFree_kick_taking(rs.getInt(i++));
			player.setHeading(rs.getInt(i++));
			player.setLong_shots(rs.getInt(i++));
			player.setLong_throws(rs.getInt(i++));
			player.setMarking(rs.getInt(i++));
			player.setPassing(rs.getInt(i++));
			player.setPenalty_taking(rs.getInt(i++));
			player.setTackling(rs.getInt(i++));
			player.setTechnique(rs.getInt(i++));

			player.setAggression(rs.getInt(i++));
			player.setAnticipation(rs.getInt(i++));
			player.setBravery(rs.getInt(i++));
			player.setComposure(rs.getInt(i++));
			player.setConcentration(rs.getInt(i++));
			player.setCreativity(rs.getInt(i++));
			player.setDecisions(rs.getInt(i++));
			player.setDetermination(rs.getInt(i++));
			player.setFlair(rs.getInt(i++));
			player.setInfluence(rs.getInt(i++));
			player.setOff_the_ball(rs.getInt(i++));
			player.setPositioning(rs.getInt(i++));
			player.setTeamwork(rs.getInt(i++));
			player.setWork_rate(rs.getInt(i++));

			player.setAcceleration(rs.getInt(i++));
			player.setAgility(rs.getInt(i++));
			player.setBalance(rs.getInt(i++));
			player.setJumping(rs.getInt(i++));
			player.setNatural_fitness(rs.getInt(i++));
			player.setPace(rs.getInt(i++));
			player.setStamina(rs.getInt(i++));
			player.setStrength(rs.getInt(i++));

			player.setConsistency(rs.getInt(i++));
			player.setDirtiness(rs.getInt(i++));
			player.setImp_matches(rs.getInt(i++));
			player.setInjury_proneness(rs.getInt(i++));
			player.setVersatility(rs.getInt(i++));

			players.add(player);
		}
		closeRS();
		System.out.println("select player count = " + players.size());
		return players;
	}

}